package com.shigen.demo.excel;

import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.shigen.demo.dao.UserMapper;
import com.shigen.demo.entity.User;
import com.shigen.demo.excel.listener.EasyExcelGeneralDataListener;
import com.shigen.demo.excel.vo.UserVo;
import com.shigen.demo.service.UserService;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

/**
 * @author shigenfu
 * @date 2023/8/20 10:44 下午
 */
@RestController
@Slf4j
@RequestMapping(value = "excel")
public class ExcelController {

    @Resource
    private UserMapper userMapper;

    @Resource
    private UserService userService;

    private final ExecutorService threadPoolExecutor = Executors.newSingleThreadExecutor();

    /**
     * 异步多线程导出
     *
     * @param response response
     * @throws IOException IOException
     */
    @GetMapping("/export")
    public void export(HttpServletResponse response) throws IOException {
        OutputStream outputStream = response.getOutputStream();
        long start = System.currentTimeMillis();
        System.out.println("-----------任务执行开始-----------");
        // 设置响应内容
        response.setContentType("application/vnd.ms-excel");
        // 防止下载的文件名字乱码
        response.setCharacterEncoding("UTF-8");
        try {
            // 文件以附件形式下载
            response.setHeader("Content-disposition",
                "attachment;filename=down_" + URLEncoder.encode(System.currentTimeMillis() + ".xlsx", "utf-8"));
            // 获取总数据量
            Long count = userMapper.selectCount(null);
            // 每页多少个
            int pageSize = 10;
            // 必须放到循环外，否则会刷新流
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
            List<CompletableFuture<Void>> completableFutures = new ArrayList<>();
            for (int i = 1; i <= (count / pageSize) + 1; i++) {
                int finalI = i;
                CompletableFuture<Void> completableFuture = CompletableFuture.runAsync(() -> {
                    List<User> exportList = userMapper.selectPage(new Page<>(finalI, pageSize), null).getRecords();
                    if (CollectionUtil.isNotEmpty(exportList)) {
                        WriteSheet writeSheet = EasyExcel.
                            writerSheet(finalI, "用户" + (finalI + 1)).head(User.class)
                            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
                        synchronized (excelWriter) {
                            excelWriter.write(exportList, writeSheet);
                        }
                    }
                }, threadPoolExecutor);
                completableFutures.add(completableFuture);
            }
            for (CompletableFuture<Void> completableFuture : completableFutures) {
                completableFuture.join();
            }
            // 刷新流
            excelWriter.finish();
            System.out.println("本次导出Excel任务执行完毕,共消耗：  " + (System.currentTimeMillis() - start) + "ms");
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            outputStream.flush();
            response.getOutputStream().close();
        }
    }

    /**
     * 针对的是id连续，根据id和pageSize查询分页
     *
     * @param response response
     * @throws IOException IOException
     */
    @GetMapping("/newExport")
    public void newExport(HttpServletResponse response) throws IOException {
        OutputStream outputStream = response.getOutputStream();
        long start = System.currentTimeMillis();
        System.out.println("-----------任务执行开始-----------");
        // 设置响应内容
        response.setContentType("application/vnd.ms-excel");
        // 防止下载的文件名字乱码
        response.setCharacterEncoding("UTF-8");
        try {
            // 文件以附件形式下载
            response.setHeader("Content-disposition",
                "attachment;filename=down_" + URLEncoder.encode(System.currentTimeMillis() + ".xlsx", "utf-8"));
            // 获取总数据量
            Long count = userMapper.selectCount(null);
            //每页多少个
            int pageSize = 100;
            // 必须放到循环外，否则会刷新流
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
            // 先查询第一页
            List<User> exportList = userMapper.selectPage(new Page<>(1, pageSize), null).getRecords();
            if (CollectionUtil.isEmpty(exportList)) {
                throw new RuntimeException("数据库没有数据");
            }
            WriteSheet writeSheet = EasyExcel.writerSheet(0, "用户" + 1).head(User.class)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
            excelWriter.write(exportList, writeSheet);

            User userEntity = exportList.get(exportList.size() - 1);
            for (int i = 1; i <= (count / pageSize) + 1; i++) {
                exportList = userMapper.findNewPage(userEntity.getId(), pageSize);
                if (CollectionUtil.isEmpty(exportList)) {
                    break;
                }
                writeSheet = EasyExcel.writerSheet(i, "用户" + (i + 1)).head(User.class)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
                excelWriter.write(exportList, writeSheet);
                userEntity = exportList.get(exportList.size() - 1);
            }
            // 刷新流
            excelWriter.finish();

            System.out.println("本次导出execl任务执行完毕,共消耗：  " + (System.currentTimeMillis() - start) + "ms");
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            outputStream.flush();
            response.getOutputStream().close();
        }
    }

    /**
     * 从excel导入100万数据到mysql
     * <p>
     * 1、首先是easyExcel分批读取Excel中的100w数据 EasyExcelGeneralDataListener按照sheet页一行行的数据读取
     * <p>
     * 2、其次就是往DB里插入，怎么去插入这20w条数据，批量插入 同样也不能使用Mybatis的批量插入，会读取数据到内存中，事务整体提交
     * <p>
     * 3、使用JDBC+事务的批量操作将数据插入到数据库（分批读取+JDBC分批插入+手动事务控制）
     * <p>
     */
    @PostMapping("/importExcel")
    public List<String> importExcel(@RequestParam("file") MultipartFile file) throws IOException {
        if (file == null || file.isEmpty()) {
            throw new RuntimeException("file为空");
        }
        InputStream inputStream = file.getInputStream();
        // 记录开始读取Excel时间,也是导入程序开始时间
        long startReadTime = System.currentTimeMillis();
        log.info("------开始读取Excel的Sheet时间(包括导入数据过程):" + startReadTime + "ms------");
        // 读取所有Sheet的数据.每次读完一个Sheet就会调用这个方法
        EasyExcelGeneralDataListener easyExcelGeneralDataListener = new EasyExcelGeneralDataListener(userService);
        EasyExcel.read(inputStream, UserVo.class, easyExcelGeneralDataListener).doReadAll();
        long endReadTime = System.currentTimeMillis();
        log.info("------结束读取耗时" + (endReadTime - startReadTime) + "ms------");
        return easyExcelGeneralDataListener.getErrorMsgList();
    }

}
